package cn.edu.zjgsu.ito.service;

import com.jfinal.kit.EncryptionKit;
import com.jfinal.plugin.activerecord.Db;

import cn.edu.zjgsu.ito.model.Payment;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
public class AdminPaymentManageService {
	/**
	 * 刘阳
	 * 返回以往缴费记录
	 * @param payment
	 * @return
	 * @throws ParseException 
	 */
//	SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
	public List<Record> looks(Record payment) throws ParseException{
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		System.out.println("12121212121212");
		String passportNo=payment.getStr("passportNo").trim();
		System.out.println(passportNo);
		if(passportNo.equals("")||passportNo==null){
			System.out.println("没有获取到该护照号");
			
		}
		//获取以往护照号
		List<Record> paymentss = Db.find("select * from payment where passportNo=?",passportNo);
		for(int i=0;i<paymentss.size();i++){
			String []b= new String[100];
			Date []date=new Date[100];
			Date dates=new Date();
			String Dates="";
			List<Record> payments = Db.find("select * from newpassportno where passportNo=?",paymentss.get(i).getStr("passportNo"));
			if(payments.size()>1){
				for(int j=0;j<payments.size();j++){
									b[j] = payments.get(j).getStr("time");
									date[j] = formatter.parse(b[j]);
								}
								System.out.println(payments.size());
								for(int a=0;a<payments.size()-1;a++){
									 dates = date[a];
									int c = (date[a+1]).compareTo(dates);
									
									if(c>0){
										dates = date[a+1];
									}
							
								}
								
								Dates = formatter.format(dates);
								
								Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
								
								paymentss.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
				}
			else{
				
				Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",paymentss.get(i).getStr("passportNo"));
				
				paymentss.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
			}		
			
		}
		System.out.println(paymentss);
		return paymentss;
	}
	/**
	 * 返回缴费记录
	 * 刘阳
	 * @param payment
	 * @return
	 */
	public Record look(Record payment){
		String passportNo=payment.getStr("passportNo").trim();
		Record payments = Db.findFirst("select * from payment where passportNo=?",passportNo);
		return payments;
	}
	//获取所有学生个人信息
		public List<Record> stuInfo() {
			List<Record> stuInfo = Db.find("select * from student_info,newpassportNo where student_info.passportNo=newpassportNo.passportNo");
			return stuInfo;
		}
		//新增学生缴费记录
		public void addPayment(String passportNoes,String payMethod,float amount,String paymentData,String invoiceDate,String remarks){
			String paying="";
			String current;
			System.out.println("qwq");
			System.out.println(passportNoes);
			String[] temp = passportNoes.split(",");
			System.out.println("haha");
			for(int i = 0;i < temp.length;i++){
				System.out.println("lalalal");
				if(temp[i] != null){
					System.out.println("iiiiiiiiiiii");
					Record firstName1 = Db.findFirst("select firstName from student_info where passportNo=?",temp[i]);
					Record lastName1 =  Db.findFirst("select lastName from student_info where passportNo=?",temp[i]);
					Record sNo1 = Db.findFirst("select sNo from student_info where passportNo=?",temp[i]);
					String firstName = firstName1.getStr("firstName");
					String lastName = lastName1.getStr("lastName");
					String sNo = sNo1.getStr("sNo");
					Record stu = new Record()
					.set("passportNo",temp[i])
					.set("payMethod",payMethod)
					.set("amount",amount)
					.set("paymentData",paymentData)
					.set("invoiceDate",invoiceDate)
					.set("firstName",firstName)
					.set("lastName",lastName)
					.set("sNo",sNo)
					.set("remarks",remarks);
					if(payMethod!=null){
						System.out.println("进入了");
						paying="是";
						int a;
						List<Record> paymentExist = Db.find("select * from payment where passportNo=?",temp[i]);
						System.out.println("666666666");
						if(paymentData.equals("一次性缴清")){
							a = 1;
							System.out.println("00000000");
						}
						else{
							System.out.println("*******************");
							Record b = Db.findFirst("select current from term where termValue=?",paymentData);	
							System.out.println(b);
							a = b.getInt("current");
							System.out.println("qwe");
							System.out.println(a);
						}
						if(a==1){
							 current = "1";
							 System.out.println("4444444");
						}
						else{
							current = "0";
							System.out.println("55555555");
						}
						System.out.println("1414141414114");   
						stu.set("current", current);
						stu.set("paying", paying);
					}		
					else{
						paying = "否";
						int a;
						List<Record> paymentExist = Db.find("select * from payment where passportNo=?",temp[i]);
						if(paymentData=="一次性付清"){
							a = 1;
						}
						else{
							Record b = Db.findFirst("select current from term where termValue=?",paymentData);	
							a = stu.getInt("b");
						}
						if(a==1){
							 current = "1";
						}
						else{
							current = "0";
						}
						stu.set("current", current);
						stu.set("paying", paying);
					}
					System.out.println("chulele");
					Db.save("payment",stu);
					System.out.println("uuuuuuuuuuuu");
				}
			}
			System.out.println("存了吗");
		}
/**
 * 添加缴费信息
 * liuyang
 */
	public String updatePayment(Record payment){
		System.out.println("竟来了");
//		int id=payment.getInt("id");
		String paymentData=payment.getStr("paymentData").trim();
		String amount=payment.getStr("amount").trim();
		System.out.println(amount);
		String payMethod=payment.getStr("payMethod").trim(); 
		String invoiceDate=payment.getStr("invoiceDate").trim();
		String remarks=payment.getStr("remarks").trim();
		String passportNo=payment.getStr("passportNo").trim();
		System.out.println(passportNo);
		String error="";
		String paying="";
		String current;
		System.out.println("[[[[[[[[[[[[[[!!!!!!!!!!!!!!!]]]]]]]]]");
		System.out.println(passportNo);
		//判断非空字段
		if(
				paymentData.equals("")||paymentData==null||
				amount.equals("")||amount==null||
				payMethod.equals("")||payMethod==null||
				invoiceDate.equals("")||invoiceDate==null
				
				)
		{
			error = "请填写所有必填信息!";
			return error;
		}
		System.out.println("1313313131313131313");
		if(payMethod!=null){
			System.out.println("进入了");
			paying="是";
			int a;
			List<Record> paymentExist = Db.find("select * from payment where passportNo=?",passportNo);
			System.out.println("666666666");
			if(paymentData.equals("一次性缴清")){
				a = 1;
				System.out.println("00000000");
			}
			else{
				System.out.println("*******************");
				Record b = Db.findFirst("select current from term where termValue=?",paymentData);	
				System.out.println(b);
				a = b.getInt("current");
				System.out.println("qwe");
				System.out.println(a);
			}
			if(a==1){
				 current = "1";
				 System.out.println("4444444");
			}
			else{
				current = "0";
				System.out.println("55555555");
			}
			System.out.println("1414141414114");   
			payment.set("current", current);			
			System.out.println(payment.get("current"));
		}		
		else{
			paying = "否";
			int a;
			List<Record> paymentExist = Db.find("select * from payment where passportNo=?",passportNo);
			if(paymentData=="一次性付清"){
				a = 1;
			}
			else{
				Record b = Db.findFirst("select current from term where termValue=?",paymentData);	
				a = payment.getInt("b");
			}
			if(a==1){
				 current = "1";
			}
			else{
				current = "0";
			}
			payment.set("current", current);
		}
		System.out.println("lalala"+payment);
		System.out.println("lalala   "+payment.get("lastName"));
//		payment.update();
		String [] cells = new String[11];
		for(int i=0;i<10;i++)
			cells[i]="";
		System.out.println("lalaalalalalala");
		cells[0]=passportNo;
		System.out.println(cells[0]);
		cells[1]=payment.getStr("lastName");
		System.out.println(cells[1]);
		cells[2]=payment.getStr("firstName");
		cells[3]=paymentData;
		System.out.println(cells[3]);
		cells[4]=amount;
		cells[5]=payMethod;
		cells[6]=invoiceDate;
		System.out.println(cells[6]);
		cells[7]=paying;
		cells[8]=payment.getStr("sNo");
		cells[9]=remarks;
		System.out.println(cells[9]);
		cells[10]=payment.get("current");
		System.out.println(cells[10]);
		boolean WARMING=updateTablesByNo1(cells,passportNo);
		System.out.println("12232323");
		if(WARMING==false){
			System.out.println("出事了");
		}
		error="修改成功";
		System.out.println(error);
		return error;
	}
	/**
	 * 缴费管理
	 * 刘阳
	 * @param fileName
	 * @param newFileName
	 * @param filePath
	 * @return
	 * @throws ParseException 
	 * @throws BiffException
	 * @throws IOException
	 */
	public List<Record> paymentManage() throws ParseException{
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		List<Record> payment = Db.find("select * from payment");
		for(int i=0;i<payment.size();i++){
		String []b= new String[100];
		Date []date=new Date[100];
		Date dates=new Date();
		String Dates="";
		List<Record> payments = Db.find("select * from newpassportno where passportNo=?",payment.get(i).getStr("passportNo"));
		if(payments.size()>1){
			for(int j=0;j<payments.size();j++){
								b[j] = payments.get(j).getStr("time");
								date[j] = formatter.parse(b[j]);
							}
							System.out.println(payments.size());
							for(int a=0;a<payments.size()-1;a++){ 
								 dates = date[a];
								int c = (date[a+1]).compareTo(dates);
								if(c>0){
									dates = date[a+1];
								}								
							}
							Dates = formatter.format(dates);
							Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
							payment.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
			}
		else{		
			Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",payment.get(i).getStr("passportNo"));				
			payment.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
		    }		
		}
		System.out.println("皮皮怪");
		return payment;
		//当前学期缴费信息
//		Record termValue = Db.findFirst("select termValue from term where current=?",1);
//		String paymentData = termValue.getStr("termValue"); 
//		System.out.println(paymentData);
//		String y = "一次性缴清";
//		String x = " ";
//		List<Record> payment = Db.find("select * from payment where paymentData=? or paymentData=? or paymentData=?",x,paymentData,y);
//		System.out.println(payment);
//		System.out.println(payment.size());
//		for(int i=0;i<payment.size();i++){
//			String []b= new String[100];
//			Date []date=new Date[100];
//			Date dates=new Date();
//			String Dates="";
//			List<Record> payments = Db.find("select * from newpassportno where passportNo=?",payment.get(i).getStr("passportNo"));
//			if(payments.size()>1){
//				for(int j=0;j<payments.size();j++){
//									b[j] = payments.get(j).getStr("time");
//									date[j] = formatter.parse(b[j]);
//								}
//								System.out.println(payments.size());
//								for(int a=0;a<payments.size()-1;a++){
//									 dates = date[a];
//									int c = (date[a+1]).compareTo(dates);
//									if(c>0){
//										dates = date[a+1];
//									}								
//								}
//								Dates = formatter.format(dates);
//								Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
//								payment.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
//				}
//			else{		
//				Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",payment.get(i).getStr("passportNo"));				
//				payment.get(i).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
//			}		
//			
//		}
	}
	/**
	 * 刘阳
	 * 缴费周期
	 */
	public List<String> paymentdataManage(String eduStartDates){
		int data = Integer.parseInt(eduStartDates);
		 List<String> list = new ArrayList<String>();
		for(int i=1;i<5;i++){
			for(int j = 1;j < 3;j++){
				String temp = (data+i-1) + "/" + (data+i) + "(" +j + ")";
				list.add(temp);
			}
		}
		list.add("一次性缴清");
		return list;
	}
	public String readPaymentTemplate(String fileName,String newFileName,String filePath) throws BiffException, IOException
	{
		Date date=new Date();
		DateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int i=0; //列
		int j=1; //行
		int k=-1;
		int rows=0;
		int breakRows=1;
		int breakExcel=1;
		int breakK=0;
		int countSuccess=0;
		int countUpdate=0;
		int countFail=0;

		String error="";
		String collegeName="";
		String breakPassportNo="";
		String type="Payment Template（缴费模板）";
		String uploader="Admin（教学秘书）";
		String identity="Payment";
		String uploadTime=format.format(date);

		Record uploadFileRecord=new Record().set("type", type)
				.set("fileName", fileName)
				.set("newFileName", newFileName)
				.set("filePath", filePath)
				.set("uploader", uploader)
				.set("uploadTime", uploadTime);
		Db.save("upload_file", uploadFileRecord);

		Workbook rwb=null;
		Cell[] cells=new Cell[11];
		InputStream stream=new FileInputStream(filePath+"\\"+newFileName);
		rwb=Workbook.getWorkbook(stream);
		Sheet sheet=rwb.getSheet(0);
		cells[0]=sheet.getCell(0,j);
		rows=sheet.getRows();
		//检测EXCEL表中所有护照号是否有冲突
		//提取所有护照号
		String[] passportNos=new String[rows-1];

		while(true)
		{
			breakRows++;
			if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY)
			{
				if(breakRows==rows)
					break;
				j++;
				cells[0]=sheet.getCell(0, j);
				continue;
			}
			k++;
			passportNos[k]=cells[0].getContents().trim();			
			breakK=k;
			if(breakRows==rows)
				break;
			j++;			
			cells[0]=sheet.getCell(0, j);	
		}

		//检测冲突
		for(j=0;j<=breakK;j++)
		{
			if(breakExcel==0)
				break;
			for(k=j+1;k<=breakK;k++)
			{
				if(passportNos[j].equals(passportNos[k]))
				{
					breakExcel=0;
					breakPassportNo=passportNos[j];
					break;
				}
			}
		}
		//可以执行入库
		if(breakExcel==1)
		{
			//数据再次初始化
			breakRows=1;
			j=1;
			cells[0]=sheet.getCell(0, j);
			cells[1]=sheet.getCell(1, j);
			cells[2]=sheet.getCell(2, j);
			cells[3]=sheet.getCell(3, j);
			cells[4]=sheet.getCell(4, j);  
			cells[5]=sheet.getCell(5, j);  
			cells[6]=sheet.getCell(6, j);
			cells[7]=sheet.getCell(7, j);
			cells[8]=sheet.getCell(8, j);
			cells[9]=sheet.getCell(9, j);
			cells[10]=sheet.getCell(10, j);
			while(true)
			{
				breakRows++;
				if(cells[0].getContents().trim().equals("")||cells[0].getType()==CellType.EMPTY||
						cells[1].getContents().trim().equals("")||cells[1].getType()==CellType.EMPTY||
						cells[2].getContents().trim().equals("")||cells[2].getType()==CellType.EMPTY||
//						cells[3].getContents().trim().equals("")||cells[3].getType()==CellType.EMPTY||
//						cells[4].getContents().trim().equals("")||cells[4].getType()==CellType.EMPTY||						
						cells[7].getContents().trim().equals("")||cells[7].getType()==CellType.EMPTY||											
						cells[10].getContents().trim().equals("")||cells[10].getType()==CellType.EMPTY)
				{
					countFail++;
					j++;
					if(breakRows==rows)
						break;
					else
					{
						cells[0]=sheet.getCell(0, j);
						cells[1]=sheet.getCell(1, j);
						cells[2]=sheet.getCell(2, j);
						cells[3]=sheet.getCell(3, j);
						cells[4]=sheet.getCell(4, j);  
						cells[5]=sheet.getCell(5, j); 
						cells[6]=sheet.getCell(6, j);
						cells[7]=sheet.getCell(7, j);
						cells[8]=sheet.getCell(8, j);
						cells[9]=sheet.getCell(9, j);
						cells[10]=sheet.getCell(10, j);
					}
					continue;
				}
				List<Record> paymentInfo=Db.find("select * from payment");
				//冲突检测开始，若目标护照号已存在则覆盖原记录
				for(i=0;i<paymentInfo.size();i++)
				{
					Record singlePaymentInfo=paymentInfo.get(i);
					String passportNo=singlePaymentInfo.get("passportNo");
					if(passportNo.equals(cells[0].getContents().trim()))				
						break;				
				}
				//冲突不存在，存入数据库
//				if(i==paymentInfo.size())
//				{
					Record paymentRecord=new Record()
					.set("passportNo", cells[0].getContents().trim())
					.set("lastName", cells[1].getContents().trim())
					.set("firstName", cells[2].getContents().trim())
					.set("paymentData", cells[3].getContents().trim())
					.set("amount", cells[4].getContents().trim())
					.set("payMethod", cells[5].getContents().trim())
					.set("invoiceDate", cells[6].getContents().trim())
					.set("paying", cells[7].getContents().trim())
					.set("sNo", cells[8].getContents().trim())
					.set("remarks", cells[9].getContents().trim())
					.set("current",cells[10].getContents().trim());
					Db.save("payment", paymentRecord);

					countSuccess++;
//				}
//				//冲突存在，执行覆盖操作
//				else
//				{
//					Db.update("update payment set sNo=?,lastName=?,firstName=?," +
//				              "passportNo=?,amount=?,payMethod=?," +
//							  " invoiceDate=?,remarks=?,paymentData=?,current=?where paying=?",
//							cells[8].getContents().trim(),
//							cells[1].getContents().trim(),
//							cells[2].getContents().trim(),
//							cells[0].getContents().trim(),
//							cells[4].getContents().trim(),
//							cells[5].getContents().trim(),
//							cells[6].getContents().trim(),
//							cells[9].getContents().trim(),
//							cells[3].getContents().trim(),
//							cells[10].getContents().trim(),
//							cells[7].getContents().trim());
//					boolean WARMING=updateTablesByNo(cells[0].getContents().trim());
//					if(WARMING==false)
//						System.out.println("出事了！！！");
//					countUpdate++;
//				}
				j++;
				if(breakRows==rows)
					break;
				else
				{
					cells[0]=sheet.getCell(0,j);
					cells[1]=sheet.getCell(1,j);
					cells[2]=sheet.getCell(2,j);
					cells[3]=sheet.getCell(3,j);
					cells[4]=sheet.getCell(4,j);  
					cells[5]=sheet.getCell(5,j);  
					cells[6]=sheet.getCell(6,j);
					cells[7]=sheet.getCell(7,j);
					cells[8]=sheet.getCell(8,j);
					cells[9]=sheet.getCell(9,j);
					cells[10]=sheet.getCell(10,j);
				}
			}
			error="成功导入"+countSuccess+"条记录;成功覆盖"+countUpdate+"条记录；导入失败"+countFail+"条记录";
			return error;
		}
		else
		{
			error="所上传的Excel表中存在数据冲突——护照号"+breakPassportNo;
			return error;
		}				
	}
	public boolean updateTablesByNo(String passportNo){
		int caseone = Db.update("update payment set where passportNo=?",passportNo);
		if(caseone>0)
		{
			return true;
		}
		else
		{
			return false;
		}
	}
	public boolean updateTablesByNo1(String[] cells,String passportNo){
		int caseone=Db.update("update payment set passportNo=?,paymentData=?,amount=?,invoiceDate=?,payMethod=?,paying=?,remarks=?,current=? where passportNo=? ",
				cells[0],
				cells[3],
				cells[4],
				cells[6],
				cells[5],
		        cells[7],
		        cells[9],
		        cells[10],passportNo);
		System.out.println("进入了该方法");
		if(caseone>0){
			return true;
		}
		else
			return false;
	}
	/**
	 * 筛选
	 * 刘阳
	 * @throws ParseException 
	 */
	public List<Record> selectNew(String majorName,String clazz) throws ParseException{
		java.text.SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		List<Record> list = new ArrayList<Record>();
		List<Record> temp = new ArrayList<Record>();
		int i;
		try {
			System.out.println("进来了");
			if( (!majorName.equals("0")) && (!clazz.equals("0"))){
				System.out.println("jianglaile");
				List<Record> liststudent=Db.find("select * from student_info where majorName=? and class",majorName,clazz);
				System.out.println(liststudent);
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
				}
				for(int j=0;j<temp.size();j++){
					 Record two = Db.findFirst("select * from payment where passportNo=?",temp.get(j).getStr("passportNo"));
					 if(two!=null)
					 list.add(two);
				}	
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
											b[jj] = payments.get(jj).getStr("time");
											date[jj] = formatter.parse(b[jj]);
										}
										System.out.println(payments.size());
										for(int a=0;a<payments.size()-1;a++){
											 dates = date[a];
											int c = (date[a+1]).compareTo(dates);
											if(c>0){
												dates = date[a+1];
											}
										}
										Dates = formatter.format(dates);
										Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
										list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
						}
					else{
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}	
				}
				return list;
			}
			else if( (majorName.equals("0")) && (!clazz.equals("0")) ){
				List<Record> liststudent=Db.find("select * from student_info where class=?",clazz);
//				List<Record> list = new ArrayList<Record>();
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
				}
				for(int j=0;j<temp.size();j++){
					 Record two = Db.findFirst("select * from payment where passportNo=?",temp.get(j).getStr("passportNo"));
					 if(two!=null)
					 list.add(two);
				}		
				for(int v=0;v<list.size();v++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> lists = Db.find("select * from newpassportno where passportNo=?",list.get(v).getStr("passportNo"));//
					if(lists.size()>1){
						for(int jj=0;jj<lists.size();jj++){
											b[jj] = lists.get(jj).getStr("time");
											date[jj] = formatter.parse(b[jj]);
										}
										System.out.println(lists.size());
										for(int a=0;a<lists.size()-1;a++){
											 dates = date[a];
											int c = (date[a+1]).compareTo(dates);
											if(c>0){
												dates = date[a+1];
											}
										}
										Dates = formatter.format(dates);
										Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
										list.get(v).set("newpassportNo",newpassportNo.getStr("newpassportNo"));//
						}
					else{
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(v).getStr("passportNo"));
						list.get(v).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}					
				}
				return list;
			}
			else if( (!majorName.equals("0")) && (clazz.equals("0")) ){
				List<Record> liststudent=Db.find("select * from student_info where majorName=?",majorName);
				System.out.println(liststudent);
				for(i=0;i < liststudent.size();i++){
					Record one = new Record().set("passportNo",liststudent.get(i).getStr("passportNo"));
					temp.add(one);
				}
				for(int j=0;j<temp.size();j++){
					 Record two = Db.findFirst("select * from payment where passportNo=?",temp.get(j).getStr("passportNo"));
					 if(two!=null)
					 list.add(two);
					 
				}	
				for(int v=0;v<list.size();v++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> lists = Db.find("select * from newpassportno where passportNo=?",list.get(v).getStr("passportNo"));
					if(lists.size()>1){
						for(int jj=0;jj<lists.size();jj++){
											b[jj] = lists.get(jj).getStr("time");
											date[jj] = formatter.parse(b[jj]);
										}
										System.out.println("tianya");
										System.out.println(lists.size());
										for(int a=0;a<lists.size()-1;a++){
											 dates = date[a];
											int c = (date[a+1]).compareTo(dates);
											if(c>0){
												dates = date[a+1];
											}
										}
										Dates = formatter.format(dates);
										Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
										list.get(v).set("newpassportNo",newpassportNo.getStr("newpassportNo"));//
						}
					else{
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(v).getStr("passportNo"));
						list.get(v).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}
				}
				return list;
			}
			else{
//				List<Record> list = Db.find("select * from payment");
				System.out.println("全部");
				list = Db.find("select * from payment");
				for(int ii=0;ii<list.size();ii++){
					String []b= new String[100];
					Date []date=new Date[100];
					Date dates=new Date();
					String Dates="";
					List<Record> payments = Db.find("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
					if(payments.size()>1){
						for(int jj=0;jj<payments.size();jj++){
											b[jj] = payments.get(jj).getStr("time");
											date[jj] = formatter.parse(b[jj]);
										}
										System.out.println(payments.size());
										for(int a=0;a<payments.size()-1;a++){
											 dates = date[a];
											int c = (date[a+1]).compareTo(dates);
											if(c>0){
												dates = date[a+1];
											}
										}
										Dates = formatter.format(dates);
										Record newpassportNo = Db.findFirst("select * from newpassportno where time=?",Dates);
										list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
						}
					else{
						Record newpassportNo = Db.findFirst("select * from newpassportno where passportNo=?",list.get(ii).getStr("passportNo"));
						list.get(ii).set("newpassportNo",newpassportNo.getStr("newpassportNo"));
					}	
				}
				return list;
			}			
		} catch (Exception e) {
			System.out.println("来这里了");
			e.printStackTrace();
			return null;
		}
	}
	
}
 